using System;
using System.Data;
using System.Collections.Generic;
using System.Globalization;
using System.Text;

using Kenly.DBFramework.Gateway;

namespace Kenly.DBFramework.Reflection
{
    /// <summary>
    /// Mapping the properties to sql statements .
    /// </summary>
    internal class SqlServerCommandReflector : CommandReflector
    {

        public override string Exists(Type entityType, string condition)
        {
            ValidateType(entityType);
            if (string.IsNullOrEmpty(condition))
            {
                condition = " null=null ";
            }
            return string.Concat("SELECT CASE WHEN EXISTS(SELECT * FROM " , AttributeUtility.ReflectTableName(entityType),
            " WHERE " , condition,")THEN 1 ELSE 0 END");
        }


        public override string SelectNextIdentity(Type entityType)
        {
            ValidateType(entityType);
            string identityName = IdentityUtility.GetIdentityName(entityType);
            if (string.IsNullOrEmpty(identityName))
            {
                throw new GatewayException(MsgResource.IdentityOfEntityMissing, entityType);
            }
            string tableName = AttributeUtility.ReflectTableName(entityType);
            return string.Concat("SELECT MAX(", identityName, ")+1 FROM ", tableName);
        }


        /// <summary>
        /// Reflect insert statement from entity's properites . Format : INSERT INTO TABLE(C1,C2) VALUES(@C1,@C2)
        /// </summary>
        /// <param name="entityType"></param>
        /// <param name="returnIdentity"></param>
        /// <param name="propertyNames"></param>
        /// <returns>Format : INSERT INTO TABLE(C1,C2) VALUES(@C1,@C2)</returns>
        public override string Insert(Type entityType, bool returnIdentity, List<string> propertyNames)
        {
            if (propertyNames == null || propertyNames.Count <= 0)
            {
                throw new GatewayException(MsgResource.InvalidArguments, "List<string> propertyNames");
            }

            ValidateType(entityType);
            string tableName = AttributeUtility.ReflectTableName(entityType);
            string identityName = IdentityUtility.GetIdentityName(entityType);
            string columnNameList = ColumnMapper.InsertColumnNameList(entityType, propertyNames);//not including indentity
            string parameterList = ColumnMapper.InsertParameterList(DatabaseType.SqlServer, entityType, propertyNames);//not including indentity
            if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(columnNameList) || string.IsNullOrEmpty(parameterList))
            {
                throw new GatewayException(MsgResource.InvalidEntityConfig, entityType);
            }

            //inserts a row
            string insertStatement = "INSERT INTO " + tableName + "(" + columnNameList +
                ")VALUES(" + parameterList + ")";

            //gets the identity of the new row
            if (returnIdentity && !string.IsNullOrEmpty(identityName))
            {
                insertStatement += " SET " + "@" + identityName + "=@@identity";
            }

            return insertStatement;
        }

       


        //private static string SelectPagingForSqlServer(Type entityType, string condition, Order order, int pageSize, int pageIndex)
        //{
        //    if (entityType == null)
        //    {
        //        throw new GatewayException(MsgResource.EmptyEntity);
        //    }
        //    Order pagingOrder = GetPagingOrder(entityType, order);
        //    if (pageIndex < 0)
        //    {
        //        pageIndex = 0;
        //    }
        //    string tableName = AttributeUtility.ReflectTableName(entityType);
        //    if (string.IsNullOrEmpty(condition))
        //    {
        //        condition = " null=null ";
        //    }

        //    string statement = "SELECT TOP " + pageSize + " " + ColumnMapper.SelectAllColumnNameList(entityType) + " FROM " + tableName +
        //        " WHERE " + condition + " AND " + pagingOrder.OrderColumn +
        //        " NOT IN (SELECT TOP " + pageSize * pageIndex + " " + pagingOrder.OrderColumn + " FROM " + tableName +
        //        " WHERE " + condition + " ORDER BY " + pagingOrder.OrderString + ") ORDER BY " + pagingOrder.OrderString;

        //    return statement;
        //}


        /// <summary>
        /// Only can be used with SQL Server2005 or later version.
        /// </summary>
        /// <param name="entityType"></param>
        /// <param name="condition"></param>
        /// <param name="order"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="propertyNames"></param>
        /// <returns></returns>
        public override string SelectPaging(Type entityType, string condition, Order order, int pageSize, int pageIndex, List<string> propertyNames)
        {
            if (entityType == null)
            {
                throw new GatewayException(MsgResource.EmptyEntity);
            }
            if (order == null || string.IsNullOrEmpty(order.OrderColumn) || string.IsNullOrEmpty(order.OrderString))
            {
                throw new GatewayException(MsgResource.SortedOrderRequired);
            }

            if (pageIndex < 0)
            {
                pageIndex = 0;
            }
            string tableName = AttributeUtility.ReflectTableName(entityType);
            if (string.IsNullOrEmpty(condition))
            {
                condition = " null=null ";
            }

            string columnNameList = null;
            if (propertyNames == null || propertyNames.Count <= 0)
            {
                columnNameList = ColumnMapper.SelectAllColumnNameList(entityType);
            }
            else
            {
                columnNameList = ColumnMapper.SelectColumnNameList(entityType, propertyNames);
            }

            string statement = "SELECT RowNumber," + columnNameList +
                " FROM (SELECT " + columnNameList + ",ROW_NUMBER() OVER (ORDER BY " + order.OrderString + ") AS RowNumber FROM " + tableName;
            statement += " WHERE " + condition;
            statement += " ) AS T WHERE T.RowNumber > (" + pageSize * pageIndex + ")  AND T.RowNumber <= (" + (pageIndex + 1) * pageSize + ")";

            return statement;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entityType"></param>
        /// <param name="condition"></param>
        /// <param name="order"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="propertyNames"></param>
        /// <returns></returns>
        public static string SelectPaging2000(Type entityType, string condition, Order order, int pageSize, int pageIndex, List<string> propertyNames)
        {
            if (entityType == null)
            {
                throw new GatewayException(MsgResource.EmptyEntity);
            }
            if (order == null || string.IsNullOrEmpty(order.OrderColumn) || string.IsNullOrEmpty(order.OrderString))
            {
                throw new GatewayException(MsgResource.SortedOrderRequired);
            }

            if (pageIndex < 0)
            {
                pageIndex = 0;
            }
            string tableName = AttributeUtility.ReflectTableName(entityType);
            if (string.IsNullOrEmpty(condition))
            {
                condition = " null=null ";
            }

            string columnNameList = null;
            if (propertyNames == null || propertyNames.Count <= 0)
            {
                columnNameList=ColumnMapper.SelectAllColumnNameList(entityType);
            }
            else
            {
                columnNameList=ColumnMapper.SelectColumnNameList(entityType, propertyNames);
            }

            string statement = "SELECT TOP " + pageSize + " " + columnNameList + " FROM " + tableName +
                " WHERE " + condition + " AND " + order.OrderColumn +
                " NOT IN (SELECT TOP " + pageSize * pageIndex + " " + order.OrderColumn + " FROM " + tableName +
                " WHERE " + condition + " ORDER BY " + order.OrderString + ") ORDER BY " + order.OrderString;

            return statement;
        }


        public override string SelectTop(Type entityType, string condition, int topCount, Order order, List<string> propertyNames)
        {
            ValidateType(entityType);
            if (string.IsNullOrEmpty(condition))
            {
                condition = " null=null ";
            }
            StringBuilder sql = new StringBuilder();

            sql.Append("SELECT ");
            sql.Append((topCount > 0) ? (" TOP " + topCount.ToString(System.Globalization.CultureInfo.CurrentCulture) + " ") : "");
            if (propertyNames == null || propertyNames.Count <= 0)
            {
                sql.Append(ColumnMapper.SelectAllColumnNameList(entityType));
            }
            else
            {
                sql.Append(ColumnMapper.SelectColumnNameList(entityType, propertyNames));
            }
            sql.Append(" FROM ");
            sql.Append(AttributeUtility.ReflectTableName(entityType) + "(NOLOCK)");
            sql.Append(" WHERE " + condition);
            if (order != null && !string.IsNullOrEmpty(order.OrderString))
            {
                sql.Append(" ORDER BY " + order.OrderString);
            }

            return sql.ToString();
        }

        protected override string GetUpdateColumnParameterPairs(Type entityType, bool updatedByPK, List<string> propertyNames)
        {
            return ColumnMapper.UpdateColumnParameterPairs(DatabaseType.SqlServer, entityType, updatedByPK, propertyNames);
        }

       
    }
}
